{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 该类型特征的 column name 都以 CB_ 开头，意为Class and Brand（综合了车型和品牌）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pandas.tseries.offsets import *\n",
    "from xiao_utils import f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 20297 entries, 0 to 139\n",
      "Data columns (total 32 columns):\n",
      "TR                       20157 non-null object\n",
      "brand_id                 20297 non-null int64\n",
      "car_height               20157 non-null float64\n",
      "car_length               20157 non-null float64\n",
      "car_width                20157 non-null float64\n",
      "class_id                 20297 non-null int64\n",
      "compartment              20157 non-null float64\n",
      "cylinder_number          20157 non-null float64\n",
      "department_id            20157 non-null float64\n",
      "displacement             20157 non-null float64\n",
      "driven_type_id           20157 non-null float64\n",
      "emission_standards_id    20157 non-null float64\n",
      "engine_torque            20138 non-null float64\n",
      "equipment_quality        20157 non-null float64\n",
      "front_track              20157 non-null float64\n",
      "fuel_type_id             20154 non-null float64\n",
      "gearbox_type             20157 non-null object\n",
      "if_MPV_id                20157 non-null float64\n",
      "if_charging              20157 non-null object\n",
      "if_luxurious_id          20157 non-null float64\n",
      "level_id                 19859 non-null float64\n",
      "newenergy_type_id        20157 non-null float64\n",
      "power                    20157 non-null float64\n",
      "price                    11377 non-null float64\n",
      "price_level              20157 non-null float64\n",
      "rated_passenger          20157 non-null object\n",
      "rear_track               20157 non-null float64\n",
      "sale_date                20297 non-null datetime64[ns]\n",
      "sale_quantity            20157 non-null float64\n",
      "total_quality            20157 non-null float64\n",
      "type_id                  20157 non-null float64\n",
      "wheelbase                20157 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(25), int64(2), object(4)\n",
      "memory usage: 5.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# 将level_id字段中的-替换为np.nan\n",
    "df = pd.read_csv('../../data/origin/[new] yancheng_train_20171226.csv', dtype={'sale_date':str}, na_values=['-'], low_memory=False)\n",
    "df['sale_date']= pd.to_datetime(df['sale_date'], format='%Y%m')\n",
    "\n",
    "# 将price_level字段转换成有序类别的类型，并用其数值填入该列。\n",
    "df['price_level'] = df['price_level'].astype('category', categories=['5WL','5-8W','8-10W','10-15W','15-20W','20-25W','25-35W','35-50W','50-75W'], ordered=True)\n",
    "df['price_level'] = df['price_level'].cat.codes\n",
    "\n",
    "# 待选方案：先把power和扭矩字段带/的行复制一份，然后将新行里的销量清零，将原行和新行的power和扭矩字段的值分别赋为slash前后的值。\n",
    "# 现行方案：先他娘的直接把slash和后面的值删掉。省得影响记录条数相关的统计量。\n",
    "def process_power_and_torque(s):\n",
    "    return s.split('/')[0]\n",
    "df['power'] = df['power'].astype(str).apply(process_power_and_torque).astype(float) #[18600]\n",
    "df['engine_torque'] = df['engine_torque'].astype(str).apply(process_power_and_torque).astype(float)\n",
    "\n",
    "# -------------------------------------------------------------\n",
    "# 把2017年11月的数据拼进来，一块填入其特征，用于最终输出要提交的结果。\n",
    "empty_Nov = pd.read_csv('../../data/origin/yancheng_testA_20171225.csv', dtype={'predict_date':str}, na_values=['-'], low_memory=False)\n",
    "empty_Nov['predict_date']= pd.to_datetime(empty_Nov['predict_date'], format='%Y%m')\n",
    "empty_Nov.rename(columns = {'predict_date': 'sale_date', 'predict_quantity':'sale_quantity'}, inplace = True)\n",
    "\n",
    "\n",
    "# 读取玩了，先不急着拼，先把车型到品牌的映射关系join进来\n",
    "class_to_brand = df[['class_id','brand_id']].groupby(['class_id']).mean().reset_index()\n",
    "empyt_Nov = pd.merge(left=empty_Nov, right=class_to_brand, on='class_id', how='left')\n",
    "empty_Nov['brand_id']= class_to_brand['brand_id']\n",
    "# empty_Nov\n",
    "# class_to_brand\n",
    "\n",
    "# class_to_brand\n",
    "\n",
    "# 读取完了，拼上去\n",
    "df = pd.concat([df, empty_Nov])\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "cls_to_brand = df[['class_id','brand_id']].groupby(['class_id']).first().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 注意要把表中的 0 也都按空值 nan 处理，因为实际上不可能有真正有意义的 0 出现的！\n",
    "df_C = pd.read_csv(\"../../data/features/C_features.csv\", dtype={'sale_date':str}, na_values=['-',0], low_memory=False)\n",
    "df_C['sale_date']= pd.to_datetime(df_C['sale_date'])\n",
    "# df_C = df_C.drop('C_rcm_0', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_B = pd.read_csv(\"../../data/features/B_features.csv\", dtype={'sale_date':str}, na_values=['-',0], low_memory=False)\n",
    "df_B['sale_date']= pd.to_datetime(df_B['sale_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5727 entries, 0 to 5726\n",
      "Columns: 951 entries, class_id to C_rcm_rfdy_3\n",
      "dtypes: datetime64[ns](1), float64(949), int64(1)\n",
      "memory usage: 41.6 MB\n"
     ]
    }
   ],
   "source": [
    "df_C.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1733 entries, 0 to 1732\n",
      "Columns: 507 entries, brand_id to B_rfdy_3\n",
      "dtypes: datetime64[ns](1), float64(505), int64(1)\n",
      "memory usage: 6.7 MB\n"
     ]
    }
   ],
   "source": [
    "df_B.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>class_id</th>\n",
       "      <th>sale_date</th>\n",
       "      <th>sale_quantity</th>\n",
       "      <th>C_som_1</th>\n",
       "      <th>C_som_2</th>\n",
       "      <th>C_som_3</th>\n",
       "      <th>C_som_4</th>\n",
       "      <th>C_som_5</th>\n",
       "      <th>C_som_6</th>\n",
       "      <th>C_som_7</th>\n",
       "      <th>...</th>\n",
       "      <th>C_rcm_sdy_3</th>\n",
       "      <th>C_rcm_sry_1</th>\n",
       "      <th>C_rcm_sry_2</th>\n",
       "      <th>C_rcm_sry_3</th>\n",
       "      <th>C_rcm_dfry_1</th>\n",
       "      <th>C_rcm_dfry_2</th>\n",
       "      <th>C_rcm_dfry_3</th>\n",
       "      <th>C_rcm_rfdy_1</th>\n",
       "      <th>C_rcm_rfdy_2</th>\n",
       "      <th>C_rcm_rfdy_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-03-01</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-04-01</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>226.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-06-01</td>\n",
       "      <td>286.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>297.0</td>\n",
       "      <td>286.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 951 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   class_id  sale_date  sale_quantity  C_som_1  C_som_2  C_som_3  C_som_4  \\\n",
       "0    103507 2015-03-01           58.0      NaN      NaN      NaN      NaN   \n",
       "1    103507 2015-04-01          232.0     58.0      NaN      NaN      NaN   \n",
       "2    103507 2015-05-01          226.0    232.0     58.0      NaN      NaN   \n",
       "3    103507 2015-06-01          286.0    226.0    232.0     58.0      NaN   \n",
       "4    103507 2015-07-01          297.0    286.0    226.0    232.0     58.0   \n",
       "\n",
       "   C_som_5  C_som_6  C_som_7      ...       C_rcm_sdy_3  C_rcm_sry_1  \\\n",
       "0      NaN      NaN      NaN      ...               NaN          NaN   \n",
       "1      NaN      NaN      NaN      ...               NaN          NaN   \n",
       "2      NaN      NaN      NaN      ...               NaN          NaN   \n",
       "3      NaN      NaN      NaN      ...               NaN          NaN   \n",
       "4      NaN      NaN      NaN      ...               NaN          NaN   \n",
       "\n",
       "   C_rcm_sry_2  C_rcm_sry_3  C_rcm_dfry_1  C_rcm_dfry_2  C_rcm_dfry_3  \\\n",
       "0          NaN          NaN           NaN           NaN           NaN   \n",
       "1          NaN          NaN           NaN           NaN           NaN   \n",
       "2          NaN          NaN           NaN           NaN           NaN   \n",
       "3          NaN          NaN           NaN           NaN           NaN   \n",
       "4          NaN          NaN           NaN           NaN           NaN   \n",
       "\n",
       "   C_rcm_rfdy_1  C_rcm_rfdy_2  C_rcm_rfdy_3  \n",
       "0           NaN           NaN           NaN  \n",
       "1           NaN           NaN           NaN  \n",
       "2           NaN           NaN           NaN  \n",
       "3           NaN           NaN           NaN  \n",
       "4           NaN           NaN           NaN  \n",
       "\n",
       "[5 rows x 951 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_C.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>brand_id</th>\n",
       "      <th>sale_date</th>\n",
       "      <th>sale_quantity</th>\n",
       "      <th>B_som_1</th>\n",
       "      <th>B_som_2</th>\n",
       "      <th>B_som_3</th>\n",
       "      <th>B_som_4</th>\n",
       "      <th>B_som_5</th>\n",
       "      <th>B_som_6</th>\n",
       "      <th>B_som_7</th>\n",
       "      <th>...</th>\n",
       "      <th>B_sdy_3</th>\n",
       "      <th>B_sry_1</th>\n",
       "      <th>B_sry_2</th>\n",
       "      <th>B_sry_3</th>\n",
       "      <th>B_dfry_1</th>\n",
       "      <th>B_dfry_2</th>\n",
       "      <th>B_dfry_3</th>\n",
       "      <th>B_rfdy_1</th>\n",
       "      <th>B_rfdy_2</th>\n",
       "      <th>B_rfdy_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-01-01</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-02-01</td>\n",
       "      <td>21.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-03-01</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-04-01</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>2012-05-01</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 507 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   brand_id  sale_date  sale_quantity  B_som_1  B_som_2  B_som_3  B_som_4  \\\n",
       "0        12 2012-01-01           47.0      NaN      NaN      NaN      NaN   \n",
       "1        12 2012-02-01           21.0     47.0      NaN      NaN      NaN   \n",
       "2        12 2012-03-01           10.0     21.0     47.0      NaN      NaN   \n",
       "3        12 2012-04-01           10.0     10.0     21.0     47.0      NaN   \n",
       "4        12 2012-05-01           10.0     10.0     10.0     21.0     47.0   \n",
       "\n",
       "   B_som_5  B_som_6  B_som_7    ...     B_sdy_3  B_sry_1  B_sry_2  B_sry_3  \\\n",
       "0      NaN      NaN      NaN    ...         NaN      NaN      NaN      NaN   \n",
       "1      NaN      NaN      NaN    ...         NaN      NaN      NaN      NaN   \n",
       "2      NaN      NaN      NaN    ...         NaN      NaN      NaN      NaN   \n",
       "3      NaN      NaN      NaN    ...         NaN      NaN      NaN      NaN   \n",
       "4      NaN      NaN      NaN    ...         NaN      NaN      NaN      NaN   \n",
       "\n",
       "   B_dfry_1  B_dfry_2  B_dfry_3  B_rfdy_1  B_rfdy_2  B_rfdy_3  \n",
       "0       NaN       NaN       NaN       NaN       NaN       NaN  \n",
       "1       NaN       NaN       NaN       NaN       NaN       NaN  \n",
       "2       NaN       NaN       NaN       NaN       NaN       NaN  \n",
       "3       NaN       NaN       NaN       NaN       NaN       NaN  \n",
       "4       NaN       NaN       NaN       NaN       NaN       NaN  \n",
       "\n",
       "[5 rows x 507 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_B.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_C = pd.merge(df_C, cls_to_brand, how='left', on='class_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_BC_tmp = pd.merge(df_C, df_B, how='left', on=['brand_id','sale_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 5727 entries, 0 to 5726\n",
      "Columns: 1457 entries, class_id to B_rfdy_3\n",
      "dtypes: datetime64[ns](1), float64(1454), int64(2)\n",
      "memory usage: 63.7 MB\n"
     ]
    }
   ],
   "source": [
    "df_BC_tmp.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 主要逻辑\n",
    "def calc_ratio_class_divby_brand(df_BC_tmp, df_C):\n",
    "    \"\"\"\n",
    "    Args:\n",
    "        df_BC_tmp: 辅助\n",
    "        df_C: 待扩充的目标表\n",
    "    Return:\n",
    "        tmp：基于历史销量上车型占品牌比重的信息，构造出的特征们\n",
    "    \"\"\"\n",
    "    tmp = df_C\n",
    "\n",
    "    # 过去几年内的每个月销量\n",
    "    for i in range(62):\n",
    "        c = df_BC_tmp['C_som_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_som_' + str(i+1)]\n",
    "        tmp['CB_som_' + str(i+1)] = c / b\n",
    "\n",
    "\n",
    "    # 该车型过去2~60个月分别的销量和\n",
    "    for i in range(60):\n",
    "        c = df_BC_tmp['C_ssm_' + str(i+2)]\n",
    "        b = df_BC_tmp['B_ssm_' + str(i+2)]\n",
    "        tmp['CB_ssm_' + str(i+2) ] = c / b\n",
    "    \n",
    "    # 一阶差分，一阶比值\n",
    "    for i in range(61):\n",
    "        c = df_BC_tmp['C_fd_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_fd_' + str(i+1)]\n",
    "        tmp['CB_fd_' + str(i+1)] = c/b\n",
    "        c = df_BC_tmp['C_fr_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_fr_' + str(i+1)]\n",
    "        tmp['CB_fr_' + str(i+1)] = c/b\n",
    "    \n",
    "    # 二阶差分\n",
    "    for i in range(60):\n",
    "        c = df_BC_tmp['C_sd_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_sd_' + str(i+1)]\n",
    "        tmp['CB_sd_' + str(i+1)] = c/b\n",
    "        \n",
    "    # 二阶比值\n",
    "    for i in range(60):\n",
    "        c = df_BC_tmp['C_sr_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_sr_' + str(i+1)]\n",
    "        tmp['CB_sr_' + str(i+1)] = c/b\n",
    "        \n",
    "    # 比值的差分\n",
    "    for i in range(60):\n",
    "        c = df_BC_tmp['C_dfr_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_dfr_' + str(i+1)]\n",
    "        tmp['CB_dfr_' + str(i+1)] = c/b\n",
    "    \n",
    "    # 差分的比值\n",
    "    for i in range(60):\n",
    "        c = df_BC_tmp['C_rfd_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_rfd_' + str(i+1)]\n",
    "        tmp['CB_rfd_' + str(i+1)] = c/b\n",
    "        \n",
    "        \n",
    "    # 相邻年，一阶差分，一阶比值\n",
    "    for i in range(4):\n",
    "        c = df_BC_tmp['C_fdy_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_fdy_' + str(i+1)]\n",
    "        tmp['CB_fdy_' + str(i+1)] = c/b\n",
    "        c = df_BC_tmp['C_fry_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_fry_' + str(i+1)]\n",
    "        tmp['CB_fry_' + str(i+1)] = c/b\n",
    "    \n",
    "    # 相邻年，二阶差分\n",
    "    for i in range(3):\n",
    "        c = df_BC_tmp['C_sdy_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_sdy_' + str(i+1)]\n",
    "        tmp['CB_sdy_' + str(i+1)] = c/b\n",
    "        \n",
    "    # 相邻年，二阶比值\n",
    "    for i in range(3):\n",
    "        c = df_BC_tmp['C_sry_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_sry_' + str(i+1)]\n",
    "        tmp['CB_sry_' + str(i+1)] = c/b\n",
    "        \n",
    "    # 相邻年，比值的差分\n",
    "    for i in range(3):\n",
    "        c = df_BC_tmp['C_dfry_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_dfry_' + str(i+1)]\n",
    "        tmp['CB_dfry_' + str(i+1)] = c/b\n",
    "    \n",
    "    # 相邻年，差分的比值\n",
    "    for i in range(3):\n",
    "        c = df_BC_tmp['C_rfdy_' + str(i+1)]\n",
    "        b = df_BC_tmp['B_rfdy_' + str(i+1)]\n",
    "        tmp['CB_rfdy_' + str(i+1)] = c/b\n",
    "    \n",
    "\n",
    "    \n",
    "    # 注意要把np.inf替换为空值，在上面算月销量比例时，引入了inf，其实应该作为空值。\n",
    "    # 注意过程中产生的 0 也要都换成空值！因为实际上不可能有有意义的0出现的。\n",
    "    return tmp.replace([np.inf, -np.inf, 0], np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp = calc_ratio_class_divby_brand(df_BC_tmp, df_C)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 5727 entries, 0 to 5726\n",
      "Columns: 1456 entries, class_id to CB_rfdy_3\n",
      "dtypes: datetime64[ns](1), float64(1453), int64(2)\n",
      "memory usage: 63.7 MB\n"
     ]
    }
   ],
   "source": [
    "tmp.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>class_id</th>\n",
       "      <th>sale_date</th>\n",
       "      <th>sale_quantity_x</th>\n",
       "      <th>C_som_1</th>\n",
       "      <th>C_som_2</th>\n",
       "      <th>C_som_3</th>\n",
       "      <th>C_som_4</th>\n",
       "      <th>C_som_5</th>\n",
       "      <th>C_som_6</th>\n",
       "      <th>C_som_7</th>\n",
       "      <th>...</th>\n",
       "      <th>B_sdy_3</th>\n",
       "      <th>B_sry_1</th>\n",
       "      <th>B_sry_2</th>\n",
       "      <th>B_sry_3</th>\n",
       "      <th>B_dfry_1</th>\n",
       "      <th>B_dfry_2</th>\n",
       "      <th>B_dfry_3</th>\n",
       "      <th>B_rfdy_1</th>\n",
       "      <th>B_rfdy_2</th>\n",
       "      <th>B_rfdy_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-03-01</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2370.0</td>\n",
       "      <td>0.803762</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.297090</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.638752</td>\n",
       "      <td>0.513924</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-04-01</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2434.0</td>\n",
       "      <td>0.724082</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.423400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.318985</td>\n",
       "      <td>0.534511</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>226.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2486.0</td>\n",
       "      <td>0.463462</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.016314</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.258659</td>\n",
       "      <td>0.894208</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-06-01</td>\n",
       "      <td>286.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2180.0</td>\n",
       "      <td>0.694375</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.473018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.211055</td>\n",
       "      <td>0.547706</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>297.0</td>\n",
       "      <td>286.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2634.0</td>\n",
       "      <td>0.804476</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.294399</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.629129</td>\n",
       "      <td>0.505695</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 1457 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   class_id  sale_date  sale_quantity_x  C_som_1  C_som_2  C_som_3  C_som_4  \\\n",
       "0    103507 2015-03-01             58.0      NaN      NaN      NaN      NaN   \n",
       "1    103507 2015-04-01            232.0     58.0      NaN      NaN      NaN   \n",
       "2    103507 2015-05-01            226.0    232.0     58.0      NaN      NaN   \n",
       "3    103507 2015-06-01            286.0    226.0    232.0     58.0      NaN   \n",
       "4    103507 2015-07-01            297.0    286.0    226.0    232.0     58.0   \n",
       "\n",
       "   C_som_5  C_som_6  C_som_7    ...     B_sdy_3   B_sry_1  B_sry_2  B_sry_3  \\\n",
       "0      NaN      NaN      NaN    ...      2370.0  0.803762      NaN      NaN   \n",
       "1      NaN      NaN      NaN    ...      2434.0  0.724082      NaN      NaN   \n",
       "2      NaN      NaN      NaN    ...      2486.0  0.463462      NaN      NaN   \n",
       "3      NaN      NaN      NaN    ...      2180.0  0.694375      NaN      NaN   \n",
       "4      NaN      NaN      NaN    ...      2634.0  0.804476      NaN      NaN   \n",
       "\n",
       "   B_dfry_1  B_dfry_2  B_dfry_3  B_rfdy_1  B_rfdy_2  B_rfdy_3  \n",
       "0 -0.297090       NaN       NaN  0.638752  0.513924       NaN  \n",
       "1 -0.423400       NaN       NaN  0.318985  0.534511       NaN  \n",
       "2 -1.016314       NaN       NaN -0.258659  0.894208       NaN  \n",
       "3 -0.473018       NaN       NaN  0.211055  0.547706       NaN  \n",
       "4 -0.294399       NaN       NaN  0.629129  0.505695       NaN  \n",
       "\n",
       "[5 rows x 1457 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_BC_tmp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>class_id</th>\n",
       "      <th>sale_date</th>\n",
       "      <th>sale_quantity</th>\n",
       "      <th>C_som_1</th>\n",
       "      <th>C_som_2</th>\n",
       "      <th>C_som_3</th>\n",
       "      <th>C_som_4</th>\n",
       "      <th>C_som_5</th>\n",
       "      <th>C_som_6</th>\n",
       "      <th>C_som_7</th>\n",
       "      <th>...</th>\n",
       "      <th>CB_sdy_3</th>\n",
       "      <th>CB_sry_1</th>\n",
       "      <th>CB_sry_2</th>\n",
       "      <th>CB_sry_3</th>\n",
       "      <th>CB_dfry_1</th>\n",
       "      <th>CB_dfry_2</th>\n",
       "      <th>CB_dfry_3</th>\n",
       "      <th>CB_rfdy_1</th>\n",
       "      <th>CB_rfdy_2</th>\n",
       "      <th>CB_rfdy_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-03-01</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-04-01</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>226.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-06-01</td>\n",
       "      <td>286.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>103507</td>\n",
       "      <td>2015-07-01</td>\n",
       "      <td>297.0</td>\n",
       "      <td>286.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>232.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 1456 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   class_id  sale_date  sale_quantity  C_som_1  C_som_2  C_som_3  C_som_4  \\\n",
       "0    103507 2015-03-01           58.0      NaN      NaN      NaN      NaN   \n",
       "1    103507 2015-04-01          232.0     58.0      NaN      NaN      NaN   \n",
       "2    103507 2015-05-01          226.0    232.0     58.0      NaN      NaN   \n",
       "3    103507 2015-06-01          286.0    226.0    232.0     58.0      NaN   \n",
       "4    103507 2015-07-01          297.0    286.0    226.0    232.0     58.0   \n",
       "\n",
       "   C_som_5  C_som_6  C_som_7    ...      CB_sdy_3  CB_sry_1  CB_sry_2  \\\n",
       "0      NaN      NaN      NaN    ...           NaN       NaN       NaN   \n",
       "1      NaN      NaN      NaN    ...           NaN       NaN       NaN   \n",
       "2      NaN      NaN      NaN    ...           NaN       NaN       NaN   \n",
       "3      NaN      NaN      NaN    ...           NaN       NaN       NaN   \n",
       "4      NaN      NaN      NaN    ...           NaN       NaN       NaN   \n",
       "\n",
       "   CB_sry_3  CB_dfry_1  CB_dfry_2  CB_dfry_3  CB_rfdy_1  CB_rfdy_2  CB_rfdy_3  \n",
       "0       NaN        NaN        NaN        NaN        NaN        NaN        NaN  \n",
       "1       NaN        NaN        NaN        NaN        NaN        NaN        NaN  \n",
       "2       NaN        NaN        NaN        NaN        NaN        NaN        NaN  \n",
       "3       NaN        NaN        NaN        NaN        NaN        NaN        NaN  \n",
       "4       NaN        NaN        NaN        NaN        NaN        NaN        NaN  \n",
       "\n",
       "[5 rows x 1456 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 存盘\n",
    "tmp.to_csv(\"../../data/features/CB_features.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
